﻿--USE [EsbExceptionDb]
--GO

--/****** Object:  StoredProcedure [dbo].[utl_DropRole]    Script Date: 01/25/2011 16:31:39 ******/
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utl_DropRole]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[utl_DropRole]
--GO

--USE [EsbExceptionDb]
--GO

--/****** Object:  StoredProcedure [dbo].[utl_DropRole]    Script Date: 01/25/2011 16:31:39 ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO


--CREATE PROCEDURE [dbo].[utl_DropRole]
--	@RoleName sysname
--AS
--BEGIN

--	SET NOCOUNT ON;

--	/****** DROP ROLE MEMBERS ******/
--	IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = @RoleName AND issqlrole = 1)
--	Begin

--		DECLARE @RoleMemberName sysname
--		DECLARE Member_Cursor CURSOR FOR
--		select [name]
--		from dbo.sysusers 
--		where uid in ( 
--			select memberuid
--			from dbo.sysmembers
--			where groupuid in (
--				select uid
--				FROM dbo.sysusers where [name] = @RoleName AND issqlrole = 1))

--		OPEN Member_Cursor;

--		FETCH NEXT FROM Member_Cursor
--		into @RoleMemberName

--		WHILE @@FETCH_STATUS = 0
--		BEGIN

--			exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

--			FETCH NEXT FROM Member_Cursor
--			into @RoleMemberName
--		END;

--		CLOSE Member_Cursor;
--		DEALLOCATE Member_Cursor;
		
--	end
	
--	/****** DROP ROLE  ******/
--	IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
--	BEGIN
--	DECLARE @SQLDrop NVARCHAR(max)
--	SET @SQLDrop = 'DROP ROLE ['+@RoleName+']'
--	EXECUTE sp_executesql @SQLDrop
--	END
	
--end


--GO



--USE [EsbExceptionDb]
--GO

--/****** Object:  StoredProcedure [dbo].[utl_SetupPortalPermissions]    Script Date: 01/25/2011 16:30:15 ******/
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utl_SetupPortalPermissions]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[utl_SetupPortalPermissions]
--GO

--USE [EsbExceptionDb]
--GO

--/****** Object:  StoredProcedure [dbo].[utl_SetupPortalPermissions]    Script Date: 01/25/2011 16:30:15 ******/
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO


--CREATE PROCEDURE [dbo].[utl_SetupPortalPermissions]
--AS
--BEGIN

--	SET NOCOUNT ON;

--	--1.  DROP ROLES AND ROLE MEMBERS
--	EXEC [dbo].[utl_DropRole] @RoleName='ESBPortal'
--	EXEC [dbo].[utl_DropRole] @RoleName='ESBPortalAdmin'
--	EXEC [dbo].[utl_DropRole] @RoleName='ESBSubmitNewFaults'
--	EXEC [dbo].[utl_DropRole] @RoleName='ESBBrowseAndManageFaults'

--	--2. CREATE ROLES
--	--CREATE ROLE [ESBSubmitNewFaults] AUTHORIZATION [dbo]
--	--CREATE ROLE [ESBBrowseAndManageFaults] AUTHORIZATION [dbo]

--	--3. ADD ROLE Members
--	--DECLARE @BizTalkInProcessHostUsers nvarchar(50)
--	-- TODO: change select query below to search for correct users.  "DEV_BTS_PROC_INP_HOST_USERS"
--	--SELECT @BizTalkInProcessHostUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%BizTalk Application Users%'
--	--EXEC sys.sp_addrolemember @rolename=N'ESBSubmitNewFaults', @membername=@BizTalkInProcessHostUsers 

--	--DECLARE @IISServicesUsers nvarchar(50)
--	--SELECT @IISServicesUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%svcLocal%'
--	--EXEC sys.sp_addrolemember @rolename=N'ESBBrowseAndManageFaults', @membername=@IISServicesUsers 

--	--DECLARE @IISAppUsers nvarchar(50)
--	--SELECT @IISAppUsers = [Name] FROM master.dbo.syslogins WHERE [Name] LIKE '%svcIIS%Application%'
--	--EXEC sys.sp_addrolemember @rolename=N'ESBBrowseAndManageFaults', @membername=@IISAppUsers 

--	--4. For the 3 specific SP's - assign Execute to the Submit New Faults Role.
--	--GRANT EXECUTE ON [dbo].[usp_insert_ContextProperty] To ESBSubmitNewFaults
--	--GRANT EXECUTE ON [dbo].[usp_insert_Fault] To ESBSubmitNewFaults
--	--GRANT EXECUTE ON [dbo].[usp_insert_Message] To ESBSubmitNewFaults


--	--5. For each table (non system) in database, assign select to BrowseAndManage Role
--	BEGIN
--		DECLARE @TableName		sysname
--		DECLARE @TableID        INT
--		DECLARE @SQL      NVARCHAR(MAX)
		
--		DECLARE @Tables TABLE (TableID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, TableName sysname)
		
--		INSERT INTO @Tables
--		  (
--			TableName
--		  )
--		SELECT TableName
--		FROM   dbo.fn_Tables()
		
--		SELECT @TableID = MIN(TableID)
--		FROM   @Tables
		
--		WHILE @TableID IS NOT NULL
--		BEGIN
--			SELECT @TableName = TableName
--			FROM   @Tables
--			WHERE  TableID = @TableID
		    
--			SET @SQL = 'GRANT SELECT ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
--			EXECUTE sp_executesql @SQL

--			SET @SQL = 'GRANT INSERT ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
--			EXECUTE sp_executesql @SQL

--			SET @SQL = 'GRANT UPDATE ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
--			EXECUTE sp_executesql @SQL

--			SET @SQL = 'GRANT DELETE ON ' + dbo.fn_QuoteName(@TableName) + ' TO [ESBBrowseAndManageFaults]'
--			EXECUTE sp_executesql @SQL
		    
--			SELECT @TableID = MIN(TableID)
--			FROM   @Tables
--			WHERE  TableID > @TableID
--		END
--	END


--	--6. For each proc (non system) in database, assign exec to BrowsAndManageRole roles
--	BEGIN
--		DECLARE @ProcName		sysname
--		DECLARE @ProcID         INT
--		DECLARE @ExecSQL        NVARCHAR(MAX)
		
--		DECLARE @Procs TABLE (ProcID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, ProcName sysname)
		
--		INSERT INTO @Procs
--		  (
--			ProcName
--		  )
--		SELECT ProcedureName as ProcName
--		FROM   dbo.fn_CustomProcs()
		
--		SELECT @ProcID = MIN(ProcID)
--		FROM   @Procs
		
--		WHILE @ProcID IS NOT NULL
--		BEGIN
--			SELECT @ProcName = ProcName
--			FROM   @Procs
--			WHERE  ProcID = @ProcID
		    
--			SET @ExecSQL = 'GRANT EXECUTE ON ' + dbo.fn_QuoteName(@ProcName) + ' TO [ESBBrowseAndManageFaults]'
--			EXECUTE sp_executesql @ExecSQL
		    
--			SELECT @PROCID = MIN(ProcID)
--			FROM   @Procs
--			WHERE  ProcID > @ProcID
--		END
--	END
	
		
--	--7. Assign execute permission to BrowseAndManage Role
--	GRANT SELECT ON dbo.vw_AggregatedFaults TO ESBBrowseAndManageFaults
--	GRANT SELECT ON dbo.vw_UserApplication TO ESBBrowseAndManageFaults
--END

--GO


----Execute Setup Portal Permissions
--USE [EsbExceptionDb]
--GO

--DECLARE	@return_value int

--EXEC	@return_value = [dbo].[utl_SetupPortalPermissions]

--SELECT	'Return Value' = @return_value

--GO